﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace CodeGenerate.Data
{
    public class DataOperate
    {
        public static List<Table> GetObject(string connStr, ObjectType type)
        {
            DataView dv = null;
            switch (type)
            {
                case ObjectType.View:
                    dv = GetViews(connStr);
                    break;
                case ObjectType.StoredProcedure:
                    dv = GetProcs(connStr);
                    break;
                default:
                    dv = GetTables(connStr);
                    break;
            }
            return ConvertDv2List(connStr, dv);
        }

        private static List<Table> ConvertDv2List(string connstr,DataView dv)
        {
            List<Table> items = new List<Table>();
            foreach (DataRowView row in dv)
            {
                Table item = new Table();
                item.Name = row["TABLE_NAME"].ToString();
                item.Description = GetTableRemark(connstr, item.Name);
                items.Add(item);
            }
            return items;
        }
        
        /// <summary>
        /// 获取所有数据表
        /// </summary>
        /// <returns></returns>
        private static DataSet GetAllTables(string connStr)
        {
            return SQLHelper.ExecuteDataSet(connStr, "sp_Tables", CommandType.StoredProcedure, null);
        }

        /// <summary>
        /// 获取当前表数据表
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        private static DataView GetTables(string connStr)
        {
            DataView dv = GetAllTables(connStr).Tables[0].DefaultView;
            dv.RowFilter = string.Format("Table_Type='TABLE' and TABLE_OWNER='dbo'");
            return dv;
        }

        /// <summary>
        /// 获取当前表所有视图
        /// </summary>
        /// <returns></returns>
        private static DataView GetViews(string connStr)
        {
            DataView dv = GetAllTables(connStr).Tables[0].DefaultView;
            dv.RowFilter = "Table_Type='VIEW' and TABLE_OWNER='dbo'";
            return dv;
        }

        /// <summary>
        /// 获取当前表所有存储过程
        /// </summary>
        /// <returns></returns>
        private static DataView GetProcs(string connStr)
        {
            string sql = "select NAME as TABLE_NAME,refdate from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 AND NAME NOT LIKE 'sp_%' order by name";
            DataSet ds = SQLHelper.ExecuteDataSet(connStr, sql, CommandType.Text, null);
            //DataSet ds = SQLHelper.ExecuteDataSet(connStr, "sp_stored_procedures", CommandType.StoredProcedure, null);
            DataView dv = ds.Tables[0].DefaultView;
            //dv.RowFilter = "procedure_owner='dbo'";
            return dv;
        }

        /// <summary>
        /// 获取指定表备注信息
        /// </summary>
        /// <param name="sTableName"></param>
        /// <returns></returns>
        private static string GetTableRemark(string connStr, string sTableName)
        {
            string sDes = string.Empty;
            string sql = string.Format(@"SELECT value FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', '{0}', default, default)", sTableName);
            IDataReader dr =  SQLHelper.ExecuteDataReader(connStr, sql, null);
            if (dr.Read())
            {
                sDes = dr.GetString(0);
            }
            dr.Close();
            return sDes;
        }

    }
}
